package com.info.zhiduoduo.repository.mapper.crowdsource;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.crowdsource.CrowdsourceInfoDto;
import com.info.zhiduoduo.repository.dto.evaluate.EvaluateNoticeInfoDto;
import com.info.zhiduoduo.repository.entity.crowdsource.CrowdsourceAccount;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 众包自然人账户表 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-09-04
 */
public interface CrowdsourceAccountMapper extends BaseMapper<CrowdsourceAccount> {

	@Select(" select csa.*,cs.phone,ap.name as provincename,ac.name as cityname,cf1.file_path as front, " +
			" c.compname as bindcompname,c.contactphone,c.contactaddress,c.crowdsource_type, cf2.file_path as back " +
			" from zdd_crowdsource_account csa " +
			" join zdd_crowdsource cs on cs.crowdsourceid = csa.crowdsourceid " +
			" left join zdd_company c on c.compid = csa.bindcompid " +
			" left join zdd_address ap on ap.id = csa.province " +
			" left join zdd_address ac on ac.id = csa.city " +
			" left join zdd_crowdsource_file cf1 on cf1.crowdsourceid = csa.crowdsourceid and cf1.file_type = 1 " +
			" left join zdd_crowdsource_file cf2 on cf2.crowdsourceid = csa.crowdsourceid and cf2.file_type = 2 " +
			" where csa.crowdsourceid = #{crowdsourceid} " +
			" limit 1 ")
	CrowdsourceInfoDto getInfo(@Param("crowdsourceid") int crowdsourceid);

	@Select(" select csa.*,cs.regfrom,cs.phone,ap.name as provincename,ac.name as cityname,if(cs.openid='',1,0) as registerCondition, " +
			" c.compname as bindcompname,c.contactphone,c.contactaddress,c.crowdsource_type,cf1.file_path as front,cf2.file_path as back " +
			" from zdd_crowdsource_account csa " +
			" join zdd_crowdsource cs on cs.crowdsourceid = csa.crowdsourceid " +
			" left join zdd_company c on c.compid = csa.bindcompid " +
			" left join zdd_address ap on ap.id = csa.province " +
			" left join zdd_address ac on ac.id = csa.city " +
            " left join zdd_crowdsource_file cf1 on cf1.crowdsourceid = csa.crowdsourceid and cf1.file_type = 1 " +
            " left join zdd_crowdsource_file cf2 on cf2.crowdsourceid = csa.crowdsourceid and cf2.file_type = 2 " +
			" where CONCAT(csa.realname,cs.nickname) like '%${name}%' " +
			" and IFNULL(c.compname, '') like '%${compname}%' " +
			" and if(isnull(#{registerCondition}),1=1,if(cs.openid='',1,0)=#{registerCondition}) " +
			" and if(#{realnameAuth} = 0, 1 = 1, csa.realname_auth = #{realnameAuth}) " +
			" order by csa.crowdsourceid desc ")
	List<CrowdsourceInfoDto> getList(@Param("name") String name,
									 @Param("compname") String compname,
									 @Param("realnameAuth") int realnameAuth,
									 @Param("registerCondition")Integer registerCondition);

	@Select(" select csa.*,cs.phone,ap.name as provincename,ac.name as cityname,cs.openid, " +
			" c.compname as bindcompname,c.contactphone,c.contactaddress,c.crowdsource_type,cf1.file_path as front,cf2.file_path as back " +
			" from zdd_crowdsource_account csa " +
			" join zdd_crowdsource cs on cs.crowdsourceid = csa.crowdsourceid " +
			" left join zdd_company c on c.compid = csa.bindcompid " +
			" left join zdd_address ap on ap.id = csa.province " +
			" left join zdd_address ac on ac.id = csa.city " +
			" left join zdd_crowdsource_file cf1 on cf1.crowdsourceid = csa.crowdsourceid and cf1.file_type = 1 " +
			" left join zdd_crowdsource_file cf2 on cf2.crowdsourceid = csa.crowdsourceid and cf2.file_type = 2 " +
			" where csa.bindcompid = #{compid} " +
			" and if(#{register}=0, 1=1,if(#{register}=1, cs.openid != '', cs.openid = '')) " +
			" and CONCAT(csa.realname,cs.nickname) like '%${name}%' " +
//			" and csa.realname_auth not in (1,4) " +
			" and if(#{whether} = 1, csa.realname_auth = 3 , csa.realname_auth not in (1,4)) " +
			" and csa.createtime between #{startDate} and #{endDate} " +
//			" and if(#{whether} = 1, csa.natural_number is not null, 1 = 1) " +
			" order by csa.crowdsourceid desc ")
	List<CrowdsourceInfoDto> getListByCompid(String name, int register, int compid, Date startDate, Date endDate, int whether);

	@Select(" select COUNT(*) from zdd_crowdsource_account where idno = #{idno}")
	int exists(String idno);

	@Select(" select * from zdd_crowdsource_account " +
			" where idno = #{idno}" +
			" and IF(ISNULL(#{realname}) OR #{realname} = '', 1 = 1 , realname = #{realname}) " +
			" order by acctid desc limit 1")
	CrowdsourceAccount findByIdno(String idno, String realname);

	@Select(" SELECT c.crowdsource_number FROM zdd_crowdsource_account ca " +
			" LEFT JOIN zdd_crowdsource c ON c.crowdsourceid = ca.crowdsourceid " +
			" WHERE ca.idno = #{idNo} ")
	String getNumberByIdNo(String idNo);


	/**
	 * 查询项目绑定的自然人信息
	 * @param projectid
	 * @return
	 */
	@Select(" SELECT ca.* " +
			" FROM zdd_crowdsource_project_item cpi " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = cpi.userid and cpi.user_type = 1 " +
			" WHERE cpi.projectid = #{projectid} AND cpi.deleted = 0 ")
	List<CrowdsourceAccount> queryCrowdsourceInfo(Integer projectid);

	@Select(" select " +
			" openid " +
			" from zdd_crowdsource c " +
			" join zdd_crowdsource_account ca on ca.crowdsourceid = c.crowdsourceid " +
			" where c.state = 2 and ca.realname_auth = 3 " +
			" and if(#{provinceid}=0, 1=1, ca.province=#{provinceid}) " +
			" and if(#{cityid}=0, 1=1, ca.city=#{cityid})")
	List<String> getAllValidOpenidsByAddress(int provinceid, int cityid);

	@Select(" select " +
			" openid " +
			" from zdd_crowdsource c " +
			" join zdd_crowdsource_account ca on ca.crowdsourceid = c.crowdsourceid " +
			" where c.state = 2 and ca.realname_auth = 3 ")
	List<String> getAllValidOpenids();

	@Select(" select c.crowdsourceid AS userId, c.openid AS openId, 4 AS userType " +
			" from zdd_crowdsource c " +
			" join zdd_crowdsource_account ca on ca.crowdsourceid = c.crowdsourceid " +
			" where c.state in (2,3) and ca.realname_auth = 3 ")
	List<EvaluateNoticeInfoDto> getPushEvaluateNoticeNeedData();

	@Select(" select c.openid from zdd_crowdsource c " +
			" where c.crowdsourceid = #{crowdSourceId} ")
	String getValidOpenId(@Param("crowdSourceId") int crowdSourceId);

	@Select("SELECT GROUP_CONCAT(ca.crowdsourceid) FROM zdd_crowdsource_account ca " +
			" WHERE ca.idno = #{idno} ")
	String getUserIdsByIdno(String idno);
}
